package com.dinglian.ots.service.impl;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dinglian.ots.common.ToolUtils;
import com.dinglian.ots.common.model.Constant;
import com.dinglian.ots.common.model.Page;
import com.dinglian.ots.dao.mapper.*;
import com.dinglian.ots.dao.model.*;
import com.dinglian.ots.exception.GlobalException;
import com.dinglian.ots.result.CodeMsg;
import com.dinglian.ots.service.BrandService;
import com.dinglian.ots.service.ProductService;
import com.dinglian.ots.vo.product.*;
import com.dinglian.ots.vo.user.UserVo;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.log4j.Log4j;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.session.Session;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 * @author wangbo
 */
@Log4j
@Service
public class ProductServiceImpl implements ProductService {

    @Value("${pic.path}")
    private String path;

    @Value("${pic.url}")
    private String url;

    @Autowired
    private ProductMapper productMapper;

    @Autowired
    private ColorMapper colorMapper;

    @Autowired
    private SizeMapper sizeMapper;

    @Autowired
    private SkuMapper skuMapper;

    @Autowired
    private BrandMapper brandMapper;

    @Autowired
    private SeasonMapper seasonMapper;

    @Autowired
    private CategoryMapper categoryMapper;

    @Autowired
    private CategoryRelationMapper categoryRelationMapper;

    @Autowired
    private BrandService brandService;


    @Override
    public Page getProductList(Integer brandId, Integer categoryId, String keyword, Integer pageNum, Integer pageSize) {
        Session session = SecurityUtils.getSubject().getSession();
        UserVo userVo  = (UserVo) session.getAttribute(Constant.SESSION_USER);
        if (userVo != null){
            String roleName = userVo.getRoleName();
            if (Constant.BUYER_ROLE_NAME.equals(roleName)){
                Integer userId = userVo.getUserId();
                boolean result =  brandService.isAuthSuccess(userId,brandId);
                if (!result){
                    throw new GlobalException(CodeMsg.BRAND_IS_NOT_PASS);
                }
            }
        }
        pageNum = (pageNum-1)*pageSize;

        List<ProductVo> productVos = productMapper.getProductList(brandId, categoryId, keyword,pageNum,pageSize);


        int totalSize = productMapper.getProductCount(brandId, categoryId, keyword);
        int totalPage = (totalSize +pageSize - 1) / pageSize;
        Page pageInfo = new Page();
        pageInfo.setPages(totalPage);
        pageInfo.setList(productVos);
        pageInfo.setPageNum(pageNum);
        pageInfo.setPageSize(pageSize);
        return  pageInfo;
    }

    @Override
    public PageInfo<ProductVo> getRecommendProduct(Integer pageNum, Integer pageSize, Integer categoryId) {
        PageHelper.startPage(pageNum, pageSize);
        List<ProductVo> productList = productMapper.getRecommendProduct(categoryId);
        return new PageInfo<>(productList);
    }

    @Override
    public ProductInfoVo getProductInfo(Integer userId, Integer productId) {
        Map productMap = productMapper.getProductInfo(userId, productId);
        ProductInfoVo productInfoVo = new ProductInfoVo();
        productInfoVo.setSeasonName((String) productMap.get("seasonName"));
        productInfoVo.setName((String) productMap.get("name"));
        productInfoVo.setTitle((String) productMap.get("title"));
        productInfoVo.setId(productId);
        productInfoVo.setBulk((String) productMap.get("bulk"));
        productInfoVo.setSeasonName((String) productMap.get("seasonName"));
        productInfoVo.setUnits((String) productMap.get("units"));
        productInfoVo.setThumbnail((String) productMap.get("thumbnail"));
        productInfoVo.setStylename((String) productMap.get("stylename"));
        productInfoVo.setNumber((String) productMap.get("number"));
        productInfoVo.setMaterials((String) productMap.get("materials"));
        productInfoVo.setMarkUp((String) productMap.get("markUp"));
        productInfoVo.setMarkDown((String) productMap.get("markDown"));
        productInfoVo.setFabrication((String) productMap.get("fabrication"));
        productInfoVo.setDiscount((String) productMap.get("discount"));
        productInfoVo.setDescription((String) productMap.get("description"));
        productInfoVo.setColorMinimum((Long)productMap.get("colorMinimum"));
        DecimalFormat df =new DecimalFormat("#.00");
        BigDecimal costPrice = (BigDecimal) productMap.get("costPrice");
        BigDecimal sellingPrice = (BigDecimal) productMap.get("sellingPrice");
        productInfoVo.setCostPrice(df.format(costPrice.doubleValue()) );
        productInfoVo.setSellingPrice(df.format(sellingPrice.doubleValue()));
        String images = (String) productMap.get("images");
        if (images != null) {
            JSONArray jsonArray = JSONArray.parseArray(images);
            productInfoVo.setImageList(jsonArray);
        }

        Long isCollected = (Long) productMap.get("isCollected");
        if (isCollected == 0) {
            productInfoVo.setCollected(false);
        } else {
            productInfoVo.setCollected(true);
        }
        return productInfoVo;
    }

    @Override
    public ProductSkuVo getProductSku(Integer productId) {

        Product product = productMapper.selectByPrimaryKey(productId);
        if (product == null) {
            throw new GlobalException(CodeMsg.PRODUCT_IS_NULL);
        }

        Session session = SecurityUtils.getSubject().getSession();
        UserVo userVo = (UserVo) session.getAttribute(Constant.SESSION_USER);
        if (userVo !=null){
            String roleName = userVo.getRoleName();
            if (Constant.BUYER_ROLE_NAME.equals(roleName)) {
                Integer userId = userVo.getUserId();
                boolean result = brandService.isAuthSuccess(userId, product.getBrandId());
                if (!result) {
                    throw new GlobalException(CodeMsg.BRAND_IS_NOT_PASS);
                }
            }
        }

        //获取产品颜色
        String colorName = productMapper.selectProductColor(productId);
        //获取尺码
        List<SkuVo> skuVos = skuMapper.getProductSku(productId);

        ProductSkuVo productSkuVo = new ProductSkuVo();
        productSkuVo.setColorName(colorName);
        productSkuVo.setSkuList(skuVos);
        return productSkuVo;
    }

    @Override
    public PageInfo<ProductListVo> getProductListByBrand(Integer brandId, String name, String stylename, String title, Integer seasonId, Integer categoryId, Integer subcategoryId, Integer isShow, Integer status, String upperDate, String downDate, Integer pageNum, Integer pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        List<ProductListVo> productListVos = productMapper.getProductListByBrand(brandId, name, stylename, title, seasonId, categoryId, subcategoryId, isShow, status, upperDate, downDate);
        for (int i = 0; i < productListVos.size(); i++) {
            ProductListVo productListVo = productListVos.get(i);
            List<SkuVo> productSku = skuMapper.getProductSku(productListVo.getProductId());
            productListVo.setSkuList(productSku);
        }
        return new PageInfo<>(productListVos);
    }


    @Override
    @Transactional(rollbackFor = Exception.class)
    public void addProduct(AddProductVo productVo) {

        Product product = new Product();
        product.setBrandId(productVo.getBrandId());
        product.setSeasonId(productVo.getSeasonId());
        product.setTitle(productVo.getTitle());
        product.setStylename(productVo.getStylename());
        product.setColorId(productVo.getColorId());
        product.setName(productVo.getName());
        product.setFabrication(productVo.getFabrication());
        product.setMaterials(productVo.getMaterials());
        product.setUnits(productVo.getUnits());
        product.setDiscount(productVo.getDiscount());
        product.setDescription(ToolUtils.urlDecoder(productVo.getDescription()));
        product.setBulk(productVo.getBulk());
        product.setColorMinimum(productVo.getColorMinimum());
        product.setStyleMinimum(productVo.getStyleMinimum());
        product.setMarkDown(productVo.getMarkDown());
        product.setMarkUp(productVo.getMarkUp());

        ProductExample productExample = new ProductExample();
        productExample.or().andStylenameEqualTo(productVo.getStylename()).andBrandIdEqualTo(productVo.getBrandId());
        List<Product> products = productMapper.selectByExample(productExample);
        if (!products.isEmpty()) {
            throw new GlobalException(CodeMsg.STYLENAME_IS_EXIST);
        }

        String costPrice = productVo.getCostPrice();
        String sellingPrice = productVo.getSellingPrice();

        product.setCostPrice(ToolUtils.yuanToCent(costPrice));
        product.setSellingPrice(ToolUtils.yuanToCent(sellingPrice));
        MultipartFile thumbnail = productVo.getThumbnail();
        String thumbnailPath = ToolUtils.uploadPicture(path, product.getStylename(), Constant.PRODUCT_THUMBNAIL, thumbnail);
        product.setThumbnail(url + thumbnailPath);
        MultipartFile whitePic = productVo.getWhitePic();
        String whitePicPath = ToolUtils.uploadPicture(path, product.getStylename(), Constant.PRODUCT_WHITE, whitePic);
        product.setWhitePic(url + whitePicPath);
        product.setStatus(Constant.AUDITED);
        JSONArray jsonArray = new JSONArray();
        MultipartFile[] details = productVo.getDetails();
        for (int i = 0; i < details.length; i++) {
            MultipartFile detail = details[i];
            String detailPath = ToolUtils.uploadPicture(path, product.getStylename(), Constant.PRODUCT_DETAIL, detail);
            jsonArray.add(url + detailPath);
        }
        product.setImages(jsonArray.toJSONString());
        productMapper.insertSelective(product);

        Integer productId = product.getId();

        product.setSort(productId);
        productMapper.updateByPrimaryKeySelective(product);

        Integer categoryId = productVo.getCategoryId();
        Integer subcategoryId = productVo.getSubcategoryId();
        CategoryRelation categoryRelation = new CategoryRelation();
        categoryRelation.setCategoryId(categoryId);
        categoryRelation.setSubcategoryId(subcategoryId);
        categoryRelation.setTargetId(productId);
        categoryRelation.setType(Constant.TYPE_PRODUCT);
        categoryRelationMapper.insertSelective(categoryRelation);

        String sizeList = productVo.getSizeList();
        JSONArray array = JSONArray.parseArray(sizeList);
        for (int i = 0; i < array.size(); i++) {
            JSONObject jsonObject = array.getJSONObject(i);
            Integer sizeId = jsonObject.getIntValue("sizeId");
            Integer stock = jsonObject.getIntValue("stock");
            Sku sku = new Sku();
            sku.setSizeId(sizeId);
            sku.setStock(stock);
            sku.setProductId(productId);
            sku.setCostPrice(product.getCostPrice());
            sku.setSellingPrice(product.getSellingPrice());
            sku.setIsDeleted(false);
            sku.setVersion("version1.0");
            skuMapper.insertSelective(sku);
        }

    }

    @Override
    public ProductDetailVo getProductDetail(Integer productId) {
        ProductDetailVo productDetailVo = productMapper.getProductDetail(productId);
        String images = productDetailVo.getImages();
        if (images != null) {
            productDetailVo.setImageList(JSONArray.parseArray(images));
        }
        return productDetailVo;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void editProduct(EditProductVo productVo) {
        Product product = new Product();
        product.setId(productVo.getId());
        product.setSeasonId(productVo.getSeasonId());
        product.setTitle(productVo.getTitle());
        product.setStylename(productVo.getStylename());
        product.setColorId(productVo.getColorId());
        product.setName(productVo.getName());
        product.setFabrication(productVo.getFabrication());
        product.setMaterials(productVo.getMaterials());
        product.setUnits(productVo.getUnits());
        product.setDiscount(productVo.getDiscount());
        String description = productVo.getDescription();
        System.out.println("富文本的值"+description);
        if (!StrUtil.isBlank(description)){
            System.out.println("富文本的值没变");
            product.setDescription(ToolUtils.urlDecoder(productVo.getDescription()));
        }
        product.setBulk(productVo.getBulk());
        product.setColorMinimum(productVo.getColorMinimum());
        product.setStyleMinimum(productVo.getStyleMinimum());
        product.setMarkDown(productVo.getMarkDown());
        product.setMarkUp(productVo.getMarkUp());
        product.setSort(productVo.getSort());
        String costPrice = productVo.getCostPrice();
        String sellingPrice = productVo.getSellingPrice();
        SkuExample skuExample = new SkuExample();
        skuExample.or().andProductIdEqualTo(productVo.getId());
        Sku sku = new Sku();
        sku.setProductId(productVo.getId());
        if (!StrUtil.isBlank(costPrice)){
            Integer costPriceInt = ToolUtils.yuanToCent(costPrice);
            product.setCostPrice(costPriceInt);
            sku.setCostPrice(costPriceInt);
        }
        if (!StrUtil.isBlank(sellingPrice)){
            Integer sellingPricceInt = ToolUtils.yuanToCent(sellingPrice);
            product.setSellingPrice(sellingPricceInt);
            sku.setSellingPrice(sellingPricceInt);
        }
        if (!StrUtil.isBlank(sellingPrice)||!StrUtil.isBlank(costPrice)){
            skuMapper.updateByExampleSelective(sku,skuExample);
        }

        MultipartFile thumbnail = productVo.getThumbnail();
        if (thumbnail != null) {
            String thumbnailPath = ToolUtils.uploadPicture(path, product.getStylename(), Constant.PRODUCT_THUMBNAIL, thumbnail);
            product.setThumbnail(url + thumbnailPath);
        }
        MultipartFile whitePic = productVo.getWhitePic();
        if (whitePic != null) {
            String whitePicPath = ToolUtils.uploadPicture(path, product.getStylename(), Constant.PRODUCT_WHITE, whitePic);
            product.setWhitePic(url + whitePicPath);
        }
        MultipartFile[] details = productVo.getDetails();
        if (details != null) {
            JSONArray jsonArray = new JSONArray();
            for (int i = 0; i < details.length; i++) {
                MultipartFile detail = details[i];
                String detailPath = ToolUtils.uploadPicture(path, product.getStylename(), Constant.PRODUCT_DETAIL, detail);
                jsonArray.add(url + detailPath);
            }
            product.setImages(jsonArray.toJSONString());
        }
        product.setLastUpdateTime(DateUtil.date());
        productMapper.updateByPrimaryKeySelective(product);

        Integer productId = product.getId();

        Integer categoryId = productVo.getCategoryId();
        Integer subcategoryId = productVo.getSubcategoryId();

        if (categoryId != null) {
            CategoryRelationExample categoryRelationExample = new CategoryRelationExample();
            categoryRelationExample.or().andTargetIdEqualTo(productId).andTypeEqualTo(Constant.TYPE_PRODUCT);
            List<CategoryRelation> categoryRelations = categoryRelationMapper.selectByExample(categoryRelationExample);
            CategoryRelation categoryRelation = categoryRelations.get(0);
            categoryRelation.setSubcategoryId(subcategoryId);
            categoryRelation.setCategoryId(categoryId);
            categoryRelationMapper.updateByPrimaryKeySelective(categoryRelation);
        }

    }

    @Override
    public void deleteProduct(String productIds) {
        String[] ids = productIds.split(",");
        List<Integer> productIdList = new ArrayList<>();
        for (int i = 0; i < ids.length; i++) {
            productIdList.add(Integer.valueOf(ids[i]));
        }
        Product product = new Product();
        product.setIsDeleted(true);
        ProductExample productExample = new ProductExample();
        productExample.or().andIdIn(productIdList);
        productMapper.updateByExampleSelective(product, productExample);
    }

    @Override
    public void upperDownProduct(String productIds, Boolean isShow) {

        String[] ids = productIds.split(",");
        List<Integer> productIdList = new ArrayList<>();
        for (int i = 0; i < ids.length; i++) {
            productIdList.add(Integer.valueOf(ids[i]));
        }
        Product product = new Product();
        product.setIsShow(isShow);
        product.setUpperDate(DateUtil.date());
        ProductExample productExample = new ProductExample();
        productExample.or().andIdIn(productIdList);
        productMapper.updateByExampleSelective(product, productExample);
    }

    @Override
    public SkuVo addProductSku(AddSkuVo addSkuVo) {
        Product product = productMapper.selectByPrimaryKey(addSkuVo.getProductId());
        Integer costPrice = product.getCostPrice();
        Integer sellingPrice = product.getSellingPrice();

        SkuExample skuExample = new SkuExample();
        skuExample.or().andSizeIdEqualTo(addSkuVo.getSizeId()).andProductIdEqualTo(addSkuVo.getProductId()).andIsDeletedEqualTo(false).andIsShowEqualTo(true);
        List<Sku> skus = skuMapper.selectByExample(skuExample);
        if (!skus.isEmpty()){
            throw new GlobalException(CodeMsg.SKU_IS_EXIST);
        }
        Sku sku = new Sku();
        sku.setProductId(product.getId());
        sku.setVersion("version1.0");
        sku.setCostPrice(costPrice);
        sku.setSellingPrice(sellingPrice);
        sku.setIsDeleted(false);
        sku.setSizeId(addSkuVo.getSizeId());
        sku.setStock(addSkuVo.getStock());
        skuMapper.insertSelective(sku);

        SkuVo skuVo = skuMapper.getSkuInfo(sku.getId());

        return skuVo;
    }

    @Override
    public void updateProductSku(UpdateSkuVo skuVo) {
        Integer skuId = skuVo.getSkuId();
        Integer sizeId = skuVo.getSizeId();
        Integer stock = skuVo.getStock();

        Sku sku = skuMapper.selectByPrimaryKey(skuId);
        if (!sku.getSizeId().equals(sizeId) || !sku.getStock().equals(stock)){
            sku.setIsShow(false);
            skuMapper.updateByPrimaryKeySelective(sku);

            sku.setStock(skuVo.getStock());
            sku.setSizeId(skuVo.getSizeId());
            String version = sku.getVersion();
            String newVersion = ToolUtils.getVersion(version);
            sku.setVersion(newVersion);
            sku.setUpdateTime(DateUtil.date());
            sku.setIsShow(true);
            skuMapper.insertSelective(sku);
        }
    }

    @Override
    public void deleteProductSku(Integer skuId) {
        Sku sku = new Sku();
        sku.setIsDeleted(true);
        sku.setUpdateTime(DateUtil.date());
        sku.setId(skuId);
        skuMapper.updateByPrimaryKeySelective(sku);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void batchImportProduct(MultipartFile file, Integer brandId) {
        try {
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream(), 0);
            List<Map<String, Object>> readAll = reader.readAll();
            if (readAll.isEmpty()) {
                throw new GlobalException(CodeMsg.EXCEL_NO_DATA);
            } else {
                List<String> fileNames = new ArrayList<>();
                for (int j = 1; j < readAll.size(); j++) {
                    Map<String, Object> map = readAll.get(j);
                    String[] table = Constant.PRODUCT_EXCEL_CN;

                    //名称
                    String name =ToolUtils.objectToString(map.get(table[1]));
                    if (StrUtil.isBlank(name)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[1]));
                    }
                    //季节
                    String seasonName =ToolUtils.objectToString(map.get(table[2]));
                    if (StrUtil.isBlank(seasonName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[2]));
                    }
                    //商品名称
                    String title =ToolUtils.objectToString(map.get(table[3]));
                    if (StrUtil.isBlank(title)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[3]));
                    }
                    //款式编码
                    String stylename = ToolUtils.objectToString(map.get(table[4]));
                    if (StrUtil.isBlank(stylename)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[4]));
                    }
                    ProductExample productExample = new ProductExample();
                    productExample.or().andStylenameEqualTo(stylename).andBrandIdEqualTo(brandId);
                    List<Product> products = productMapper.selectByExample(productExample);
                    if (!products.isEmpty()) {
                        throw new GlobalException(CodeMsg.STYLENAME_IS_EXIST.fillArgs(stylename));
                    }

                    fileNames.add(stylename);
                    //商品颜色
                    String colorName =ToolUtils.objectToString(map.get(table[5]));
                    if (StrUtil.isBlank(colorName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[5]));
                    }
                    //商品颜色码
                    String colorCode = ToolUtils.objectToString(map.get(table[6]));
                    //商品颜色号
                    String colorNo =ToolUtils.objectToString(map.get(table[7]));
                    //面料
                    String fabrication = ToolUtils.objectToString(map.get(table[8]));
                    //面料码
                    String fabricationCode = ToolUtils.objectToString(map.get(table[9]));
                    //材质
                    String materials =ToolUtils.objectToString(map.get(table[10]));
                    if (StrUtil.isBlank(materials)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[10]));
                    }
                    //商品描述
                    String description = ToolUtils.objectToString(map.get(table[11]));
                    //一级分类
                    String categoryName = ToolUtils.objectToString(map.get(table[12]));
                    if (StrUtil.isBlank(categoryName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[12]));
                    }
                    //二级分类
                    String subcategoryName = ToolUtils.objectToString(map.get(table[13]));
                    //尺码
                    String sizes = ToolUtils.objectToString(map.get(table[14]));
                    if (StrUtil.isBlank(sizes)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[14]));
                    }
                    Map sizeMap = new HashMap();
                    //os
                    String os = ToolUtils.objectToString(map.get(table[15]));
                    sizeMap.put("OS",os);
                    //xs
                    String xs = ToolUtils.objectToString(map.get(table[16]));
                    sizeMap.put("XS", xs);
                    //s
                    String s =  ToolUtils.objectToString(map.get(table[17]));
                    sizeMap.put("S", s);
                    //m
                    String m =  ToolUtils.objectToString(map.get(table[18]));
                    sizeMap.put("M", m);
                    //l
                    String l =  ToolUtils.objectToString(map.get(table[19]));
                    sizeMap.put("L", l);
                    //xl
                    String xl =  ToolUtils.objectToString(map.get(table[20]));
                    sizeMap.put("XL", xl);
                    //xl
                    String xxl = ToolUtils.objectToString(map.get(table[21]));
                    sizeMap.put("XXL", xxl);
                    //35
                    String thirtyFive =  ToolUtils.objectToString(map.get(table[22]));
                    sizeMap.put("35", thirtyFive);
                    //35½
                    String thirtyFiveHalf =  ToolUtils.objectToString(map.get(table[23]));
                    sizeMap.put("35½", thirtyFiveHalf);
                    //36
                    String thirtySix =  ToolUtils.objectToString(map.get(table[24]));
                    sizeMap.put("36", thirtySix);
                    //36½
                    String thirtySixHalf = ToolUtils.objectToString(map.get(table[25]));
                    sizeMap.put("36½", thirtySixHalf);
                    //37
                    String thirtySeven =  ToolUtils.objectToString(map.get(table[26]));
                    sizeMap.put("37", thirtySeven);
                    //37½
                    String thirtySevenHalf = ToolUtils.objectToString(map.get(table[27]));
                    sizeMap.put("37½", thirtySevenHalf);
                    //38
                    String thirtyEight =  ToolUtils.objectToString(map.get(table[28]));
                    sizeMap.put("38", thirtyEight);
                    //38½
                    String thirtyEightHalf =  ToolUtils.objectToString(map.get(table[29]));
                    sizeMap.put("38½", thirtyEightHalf);
                    //39
                    String thirtyNine =  ToolUtils.objectToString(map.get(table[30]));
                    sizeMap.put("39", thirtyNine);
                    //39½
                    String thirtyNineHalf =  ToolUtils.objectToString(map.get(table[31]));
                    sizeMap.put("39½", thirtyNineHalf);
                    //40
                    String forty =  ToolUtils.objectToString(map.get(table[32]));
                    sizeMap.put("40", forty);
                    //40½
                    String fortyHalf =  ToolUtils.objectToString(map.get(table[33]));
                    sizeMap.put("40½", fortyHalf);
                    //41
                    String fortyOne =  ToolUtils.objectToString(map.get(table[34]));
                    sizeMap.put("41", fortyOne);
                    //41½
                    String fortyOneHalf =  ToolUtils.objectToString(map.get(table[35]));
                    sizeMap.put("41½", fortyOneHalf);
                    //42
                    String fortyTwo =  ToolUtils.objectToString(map.get(table[36]));
                    sizeMap.put("42", fortyTwo);
                    //42½
                    String fortyTwoHalf =  ToolUtils.objectToString(map.get(table[37]));
                    sizeMap.put("42½", fortyTwoHalf);
                    //43
                    String fortyThree =  ToolUtils.objectToString(map.get(table[38]));
                    sizeMap.put("43", fortyThree);
                    //43½
                    String fortyThreeHalf =  ToolUtils.objectToString(map.get(table[39]));
                    sizeMap.put("43½", fortyThreeHalf);
                    //44
                    String fortyFour =  ToolUtils.objectToString(map.get(table[40]));
                    sizeMap.put("44", fortyFour);



                    //进货价
                    String costPrice = ToolUtils.objectToString(map.get(table[41]));
                    if ( StrUtil.isBlank(costPrice)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[41]));
                    }
                    //建议售价
                    String sellingPrice = ToolUtils.objectToString(map.get(table[42]));
                    if (StrUtil.isBlank(sellingPrice)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[42]));
                    }
                    //订货折扣
                    String markDownStr = ToolUtils.objectToString(map.get(table[43]));
                    double markDown = 0.0;
                    if (!StrUtil.isBlank(markDownStr)) {
//                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[43]));
                        BigDecimal markDownBigDecimal = new BigDecimal(markDownStr);
                        markDown = markDownBigDecimal.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
                    }

                    //倍率
                    String markUpStr = ToolUtils.objectToString(map.get(table[44]));
                    double markUp = 0.0;
                    if (!StrUtil.isBlank(markUpStr)) {
//                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[44]));
                        BigDecimal markUpBigDecimal = new BigDecimal(markUpStr);
                        markUp = markUpBigDecimal.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
                    }

                    //折扣
                    String discountStr = ToolUtils.objectToString(map.get(table[45]));
                    double discount = 0.0;
                    if (!StrUtil.isBlank(discountStr)) {
//                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[45]));
                        BigDecimal discountBigDecimal = new BigDecimal(discountStr);
                        discount = discountBigDecimal.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
                    }
                    //单位
                    String units = ToolUtils.objectToString(map.get(table[46]));
                    //单款最小订单
                    String styleMinimum =ToolUtils.objectToString(map.get(table[47]));
                    //单色最小订单
                    String colorMinimum =  ToolUtils.objectToString(map.get(table[48]));
                    //最终单款价格
                    String finalUnitCost = ToolUtils.objectToString(map.get(table[49]));
                    //订货总额
                    String totalWholesale =  ToolUtils.objectToString(map.get(table[50]));
                    //零售总额
                    String totalRetail = ToolUtils.objectToString(map.get(table[51]));

                    Product product = new Product();
                    product.setBrandId(brandId);
                    //保存季节
                    Integer seasonId = null;
                    SeasonExample seasonExample = new SeasonExample();
                    seasonExample.or().andNameEqualTo(seasonName);
                    List<Season> seasons = seasonMapper.selectByExample(seasonExample);
                    if (seasons.isEmpty()) {
                        throw new GlobalException(CodeMsg.SEASON_ERROR.fillArgs(seasonName));
                    } else {
                        seasonId = seasons.get(0).getId();
                    }
                    product.setSeasonId(seasonId);
                    product.setTitle(title);
                    product.setStylename(stylename);
                    //保存颜色
                    Integer colorId = null;
                    ColorExample colorExample = new ColorExample();
                    colorExample.or().andNameEqualTo(colorName);
                    List<Color> colors = colorMapper.selectByExample(colorExample);
                    if (colors.isEmpty()) {
                        throw new GlobalException(CodeMsg.COLOR_ERROR.fillArgs(colorName));
                    } else {
                        colorId = colors.get(0).getId();
                    }
                    product.setBulk("");
                    product.setColorId(colorId);
                    product.setName(name);
                    product.setFabrication(fabrication);
                    product.setMaterials(materials);
                    product.setUnits(units);
                    product.setMarkUp(markUp + "");
                    product.setMarkDown(markDown + "");
                    product.setDiscount(discount + "");
                    if (StrUtil.isBlank(colorMinimum)) {
                        product.setColorMinimum(0);
                    } else {
                        product.setColorMinimum(Integer.valueOf(colorMinimum));
                    }
                    product.setDescription(description);
                    BigDecimal costPriceDouble  = new BigDecimal(costPrice);
                    Integer costPriceInt = costPriceDouble.multiply(new BigDecimal(100)).intValue();
                    product.setCostPrice(costPriceInt);
                    BigDecimal sellingPriceDouble  = new BigDecimal(sellingPrice);
                    Integer sellingPriceInt = sellingPriceDouble.multiply(new BigDecimal(100)).intValue();
                    product.setSellingPrice(sellingPriceInt);
                    product.setStatus(Constant.AUDITED);

                    productMapper.insertSelective(product);

                    Integer productId = product.getId();

                    product.setSort(productId);
                    productMapper.updateByPrimaryKeySelective(product);

                    //保存
                    Integer categoryId = null;
                    CategoryExample categoryExample = new CategoryExample();
                    categoryExample.or().andNameCnEqualTo(categoryName).andParentIdEqualTo(0);
                    List<Category> categories = categoryMapper.selectByExample(categoryExample);
                    if (categories.isEmpty()) {
                        throw new GlobalException(CodeMsg.CATEGORY_ERROR.fillArgs(categoryName));
                    } else {
                        categoryId = categories.get(0).getId();
                    }
                    Integer subcategoryId = null;
                    CategoryExample subCategoryExample = new CategoryExample();
                    subCategoryExample.or().andNameCnEqualTo(subcategoryName).andParentIdEqualTo(categoryId);
                    List<Category> subcategories = categoryMapper.selectByExample(subCategoryExample);
                    if (!subcategories.isEmpty()) {
                        subcategoryId = subcategories.get(0).getId();
                    }
                    CategoryRelation categoryRelation = new CategoryRelation();
                    categoryRelation.setCategoryId(categoryId);
                    categoryRelation.setSubcategoryId(subcategoryId);
                    categoryRelation.setTargetId(productId);
                    categoryRelation.setType(Constant.TYPE_PRODUCT);
                    categoryRelationMapper.insertSelective(categoryRelation);

                    //保存sku
                    sizes = sizes.replace("，", ",");
                    sizes = sizes.trim();
                    String[] sizeList = sizes.split(",");
                    for (int i = 0; i < sizeList.length; i++) {
                        String sizeName = sizeList[i];
                        Integer sizeId = null;
                        SizeExample sizeExample = new SizeExample();
                        sizeExample.or().andValueEqualTo(sizeName.toUpperCase());
                        List<Size> sizeIds = sizeMapper.selectByExample(sizeExample);
                        if (sizeIds.isEmpty()) {
                            throw new GlobalException(CodeMsg.SIZE_ERROR.fillArgs(seasonName));
                        } else {
                            sizeId = sizeIds.get(0).getId();
                        }
                        Sku sku = new Sku();
                        sku.setProductId(productId);
                        sku.setCostPrice(costPriceInt);
                        sku.setSellingPrice(sellingPriceInt);
                        sku.setSizeId(sizeId);
                        String stock = (String) sizeMap.get(sizeName);
                        if (StrUtil.isBlank(stock)) {
                            sku.setStock(0);
                        } else {
                            sku.setStock(Integer.valueOf(stock));
                        }
                        sku.setIsDeleted(false);
                        sku.setVersion("version1.0");
                        skuMapper.insertSelective(sku);
                    }
                }
//                savePicture(file, fileNames);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void savePicture(MultipartFile multipartFile, List<String> fileNames) throws IOException {
        InputStream inputStream = multipartFile.getInputStream();
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        inputStream.close();
        List<XSSFPictureData> pictures = workbook.getAllPictures();
        for (int i = 0; i < fileNames.size(); i++) {
            XSSFPictureData pictureData = pictures.get(i);
            byte[] data = pictureData.getData();
            //获取扩展名
            String ext = pictureData.suggestFileExtension();
            String stylename = fileNames.get(i);
            String pathName = Constant.PRODUCT_THUMBNAIL + stylename;
            String fileName = pathName + "/thumbnail" + "." + ext;
            File file = new File(path + pathName);
            if (!file.exists()) {
                file.mkdirs();
            }

            FileOutputStream out = new FileOutputStream(path + fileName);

            Product product = new Product();
            product.setThumbnail(url + fileName);
            ProductExample productExample = new ProductExample();
            productExample.or().andStylenameEqualTo(stylename);
            productMapper.updateByExampleSelective(product, productExample);

            out.write(data);
            out.close();
        }
    }


    @Override
    public void test(Integer brandId) {
        File file = new File("C:\\Users\\wangbo\\Documents\\WeChat Files\\bobo0522\\FileStorage\\File\\2020-04\\template.xlsx");
        try {
            ExcelReader reader = ExcelUtil.getReader(file, 0);
            List<Map<String, Object>> readAll = reader.readAll();
            if (readAll.isEmpty()) {
                throw new GlobalException(CodeMsg.EXCEL_NO_DATA);
            } else {
                List<String> fileNames = new ArrayList<>();
                for (int j = 1; j < readAll.size(); j++) {
                    Map<String, Object> map = readAll.get(j);
                    String[] table = Constant.PRODUCT_EXCEL_CN;
                    //名称
                    String name =ToolUtils.objectToString(map.get(table[1]));
                    if (StrUtil.isBlank(name)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[1]));
                    }
                    //季节
                    String seasonName =ToolUtils.objectToString(map.get(table[2]));
                    if (StrUtil.isBlank(seasonName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[2]));
                    }
                    //商品名称
                    String title =ToolUtils.objectToString(map.get(table[3]));
                    if (StrUtil.isBlank(title)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[3]));
                    }
                    //款式编码
                    String stylename = ToolUtils.objectToString(map.get(table[4]));
                    if (StrUtil.isBlank(stylename)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[4]));
                    }
                    ProductExample productExample = new ProductExample();
                    productExample.or().andStylenameEqualTo(stylename).andBrandIdEqualTo(brandId);
                    List<Product> products = productMapper.selectByExample(productExample);
                    if (!products.isEmpty()) {
                        throw new GlobalException(CodeMsg.STYLENAME_IS_EXIST.fillArgs(stylename));
                    }

                    fileNames.add(stylename);
                    //商品颜色
                    String colorName =ToolUtils.objectToString(map.get(table[5]));
                    if (StrUtil.isBlank(colorName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[5]));
                    }
                    //商品颜色码
                    String colorCode = ToolUtils.objectToString(map.get(table[6]));
                    //商品颜色号
                    String colorNo =ToolUtils.objectToString(map.get(table[7]));
                    //面料
                    String fabrication = ToolUtils.objectToString(map.get(table[8]));
                    //面料码
                    String fabricationCode = ToolUtils.objectToString(map.get(table[9]));
                    //材质
                    String materials =ToolUtils.objectToString(map.get(table[10]));
                    if (StrUtil.isBlank(materials)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[10]));
                    }
                    //商品描述
                    String description = ToolUtils.objectToString(map.get(table[11]));
                    //一级分类
                    String categoryName = ToolUtils.objectToString(map.get(table[12]));
                    if (StrUtil.isBlank(categoryName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[12]));
                    }
                    //二级分类
                    String subcategoryName = ToolUtils.objectToString(map.get(table[13]));
                    if (StrUtil.isBlank(subcategoryName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[13]));
                    }
                    //尺码
                    String sizes = ToolUtils.objectToString(map.get(table[14]));
                    if (StrUtil.isBlank(sizes)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[14]));
                    }
                    Map sizeMap = new HashMap();
                    //os
                    String os = ToolUtils.objectToString(map.get(table[15]));
                    sizeMap.put("OS",os);
                    //xs
                    String xs = ToolUtils.objectToString(map.get(table[16]));
                    sizeMap.put("XS", xs);
                    //s
                    String s =  ToolUtils.objectToString(map.get(table[17]));
                    sizeMap.put("S", s);
                    //m
                    String m =  ToolUtils.objectToString(map.get(table[18]));
                    sizeMap.put("M", m);
                    //l
                    String l =  ToolUtils.objectToString(map.get(table[19]));
                    sizeMap.put("L", l);
                    //xl
                    String xl =  ToolUtils.objectToString(map.get(table[20]));
                    sizeMap.put("XL", xl);
                    //xl
                    String xxl = ToolUtils.objectToString(map.get(table[21]));
                    sizeMap.put("XXL", xxl);
                    //35
                    String thirtyFive =  ToolUtils.objectToString(map.get(table[22]));
                    sizeMap.put("35", thirtyFive);
                    //35½
                    String thirtyFiveHalf =  ToolUtils.objectToString(map.get(table[23]));
                    sizeMap.put("35½", thirtyFiveHalf);
                    //36
                    String thirtySix =  ToolUtils.objectToString(map.get(table[24]));
                    sizeMap.put("36", thirtySix);
                    //36½
                    String thirtySixHalf = ToolUtils.objectToString(map.get(table[25]));
                    sizeMap.put("36½", thirtySixHalf);
                    //37
                    String thirtySeven =  ToolUtils.objectToString(map.get(table[26]));
                    sizeMap.put("37", thirtySeven);
                    //37½
                    String thirtySevenHalf = ToolUtils.objectToString(map.get(table[27]));
                    sizeMap.put("37½", thirtySevenHalf);
                    //38
                    String thirtyEight =  ToolUtils.objectToString(map.get(table[28]));
                    sizeMap.put("38", thirtyEight);
                    //38½
                    String thirtyEightHalf =  ToolUtils.objectToString(map.get(table[29]));
                    sizeMap.put("38½", thirtyEightHalf);
                    //39
                    String thirtyNine =  ToolUtils.objectToString(map.get(table[30]));
                    sizeMap.put("39", thirtyNine);
                    //39½
                    String thirtyNineHalf =  ToolUtils.objectToString(map.get(table[31]));
                    sizeMap.put("39½", thirtyNineHalf);
                    //40
                    String forty =  ToolUtils.objectToString(map.get(table[32]));
                    sizeMap.put("40", forty);
                    //40½
                    String fortyHalf =  ToolUtils.objectToString(map.get(table[33]));
                    sizeMap.put("40½", fortyHalf);
                    //41
                    String fortyOne =  ToolUtils.objectToString(map.get(table[34]));
                    sizeMap.put("41", fortyOne);
                    //41½
                    String fortyOneHalf =  ToolUtils.objectToString(map.get(table[35]));
                    sizeMap.put("41½", fortyOneHalf);
                    //42
                    String fortyTwo =  ToolUtils.objectToString(map.get(table[36]));
                    sizeMap.put("42", fortyTwo);
                    //42½
                    String fortyTwoHalf =  ToolUtils.objectToString(map.get(table[37]));
                    sizeMap.put("42½", fortyTwoHalf);
                    //43
                    String fortyThree =  ToolUtils.objectToString(map.get(table[38]));
                    sizeMap.put("43", fortyThree);
                    //43½
                    String fortyThreeHalf =  ToolUtils.objectToString(map.get(table[39]));
                    sizeMap.put("43½", fortyThreeHalf);
                    //44
                    String fortyFour =  ToolUtils.objectToString(map.get(table[40]));
                    sizeMap.put("44", fortyFour);



                    //进货价
                    String costPrice = ToolUtils.objectToString(map.get(table[41]));
                    if ( StrUtil.isBlank(costPrice)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[41]));
                    }
                    //建议售价
                    String sellingPrice = ToolUtils.objectToString(map.get(table[42]));
                    if (StrUtil.isBlank(sellingPrice)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[42]));
                    }
                    //订货折扣
                    String markDownStr = ToolUtils.objectToString(map.get(table[43]));
                    double markDown = 0.0;
                    if (!StrUtil.isBlank(markDownStr)) {
//                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[43]));
                        BigDecimal markDownBigDecimal = new BigDecimal(markDownStr);
                         markDown = markDownBigDecimal.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
                    }

                    //倍率
                    String markUpStr = ToolUtils.objectToString(map.get(table[44]));
                    double markUp = 0.0;
                    if (!StrUtil.isBlank(markUpStr)) {
//                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[44]));
                        BigDecimal markUpBigDecimal = new BigDecimal(markUpStr);
                         markUp = markUpBigDecimal.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
                    }

                    //折扣
                    String discountStr = ToolUtils.objectToString(map.get(table[45]));
                    double discount = 0.0;
                    if (!StrUtil.isBlank(discountStr)) {
//                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[45]));
                        BigDecimal discountBigDecimal = new BigDecimal(discountStr);
                        discount = discountBigDecimal.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
                    }

                    //单位
                    String units = ToolUtils.objectToString(map.get(table[46]));
                    //单款最小订单
                    String styleMinimum =ToolUtils.objectToString(map.get(table[47]));
                    //单色最小订单
                    String colorMinimum =  ToolUtils.objectToString(map.get(table[48]));
                    //最终单款价格
                    String finalUnitCost = ToolUtils.objectToString(map.get(table[49]));
                    //订货总额
                    String totalWholesale =  ToolUtils.objectToString(map.get(table[50]));
                    //零售总额
                    String totalRetail = ToolUtils.objectToString(map.get(table[51]));

                    Product product = new Product();
                    product.setBrandId(brandId);
                    //保存季节
                    Integer seasonId = null;
                    SeasonExample seasonExample = new SeasonExample();
                    seasonExample.or().andNameEqualTo(seasonName);
                    List<Season> seasons = seasonMapper.selectByExample(seasonExample);
                    if (seasons.isEmpty()) {
                        throw new GlobalException(CodeMsg.SEASON_ERROR.fillArgs(seasonName));
                    } else {
                        seasonId = seasons.get(0).getId();
                    }
                    product.setSeasonId(seasonId);
                    product.setTitle(title);
                    product.setStylename(stylename);
                    //保存颜色
                    Integer colorId = null;
                    ColorExample colorExample = new ColorExample();
                    colorExample.or().andNameEqualTo(colorName);
                    List<Color> colors = colorMapper.selectByExample(colorExample);
                    if (colors.isEmpty()) {
                        throw new GlobalException(CodeMsg.COLOR_ERROR.fillArgs(colorName));
                    } else {
                        colorId = colors.get(0).getId();
                    }
                    product.setBulk("");
                    product.setColorId(colorId);
                    product.setName(name);
                    product.setFabrication(fabrication);
                    product.setMaterials(materials);
                    product.setUnits(units);
                    if (StrUtil.isBlank(totalWholesale)) {
                        product.setTotalWholesale(0);
                    } else {
                        product.setTotalWholesale(Integer.valueOf(totalWholesale));
                    }
                    if (StrUtil.isBlank(totalRetail)) {
                        product.setTotalRetail(0);
                    } else {
                        product.setTotalRetail(Integer.valueOf(totalRetail));
                    }
                    if (StrUtil.isBlank(styleMinimum)) {
                        product.setStyleMinimum(0);
                    } else {
                        product.setStyleMinimum(Integer.valueOf(styleMinimum));
                    }
                    product.setMarkUp(markUp + "");
                    product.setMarkDown(markDown + "");
                    if (StrUtil.isBlank(finalUnitCost)) {
                        product.setFinalUnitCost(0);
                    } else {
                        product.setFinalUnitCost(Integer.valueOf(finalUnitCost));
                    }
                    product.setDiscount(discount + "");
                    if (StrUtil.isBlank(colorMinimum)) {
                        product.setColorMinimum(0);
                    } else {
                        product.setColorMinimum(Integer.valueOf(colorMinimum));
                    }
                    product.setDescription(description);
                    BigDecimal costPriceDouble  = new BigDecimal(costPrice);
                    Integer costPriceInt = costPriceDouble.multiply(new BigDecimal(100)).intValue();
                    product.setCostPrice(costPriceInt);
                    BigDecimal sellingPriceDouble  = new BigDecimal(sellingPrice);
                    Integer sellingPriceInt = sellingPriceDouble.multiply(new BigDecimal(100)).intValue();
                    product.setSellingPrice(sellingPriceInt);
                    product.setStatus(Constant.AUDITED);

                    productMapper.insertSelective(product);

                    Integer productId = product.getId();
                    //保存
                    Integer categoryId = null;
                    CategoryExample categoryExample = new CategoryExample();
                    categoryExample.or().andNameCnEqualTo(categoryName).andParentIdEqualTo(0);
                    List<Category> categories = categoryMapper.selectByExample(categoryExample);
                    if (categories.isEmpty()) {
                        throw new GlobalException(CodeMsg.CATEGORY_ERROR.fillArgs(categoryName));
                    } else {
                        categoryId = categories.get(0).getId();
                    }
                    Integer subcategoryId = null;
                    CategoryExample subCategoryExample = new CategoryExample();
                    subCategoryExample.or().andNameCnEqualTo(subcategoryName).andParentIdEqualTo(categoryId);
                    List<Category> subcategories = categoryMapper.selectByExample(subCategoryExample);
                    if (subcategories.isEmpty()) {
                        throw new GlobalException(CodeMsg.CATEGORY_ERROR.fillArgs(subcategoryName));
                    } else {
                        subcategoryId = subcategories.get(0).getId();
                    }
                    CategoryRelation categoryRelation = new CategoryRelation();
                    categoryRelation.setCategoryId(categoryId);
                    categoryRelation.setSubcategoryId(subcategoryId);
                    categoryRelation.setTargetId(productId);
                    categoryRelation.setType(Constant.TYPE_PRODUCT);
                    categoryRelationMapper.insertSelective(categoryRelation);

                    //保存sku
                    sizes = sizes.replace("，", ",");
                    sizes = sizes.trim();
                    String[] sizeList = sizes.split(",");
                    for (int i = 0; i < sizeList.length; i++) {
                        String sizeName = sizeList[i];
                        Integer sizeId = null;
                        SizeExample sizeExample = new SizeExample();
                        sizeExample.or().andValueEqualTo(sizeName.toUpperCase());
                        List<Size> sizeIds = sizeMapper.selectByExample(sizeExample);
                        if (sizeIds.isEmpty()) {
                            throw new GlobalException(CodeMsg.SIZE_ERROR.fillArgs(seasonName));
                        } else {
                            sizeId = sizeIds.get(0).getId();
                        }
                        Sku sku = new Sku();
                        sku.setProductId(productId);
                        sku.setCostPrice(costPriceInt);
                        sku.setSellingPrice(sellingPriceInt);
                        sku.setSizeId(sizeId);
                        String stock = (String) sizeMap.get(sizeName);
                        if (StrUtil.isBlank(stock)) {
                            sku.setStock(0);
                        } else {
                            sku.setStock(Integer.valueOf(stock));
                        }
                        sku.setIsDeleted(false);
                        sku.setVersion("version1.0");
                        skuMapper.insertSelective(sku);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void main(String[] args) {
        File file = new File("C:\\Users\\wangbo\\Documents\\WeChat Files\\bobo0522\\FileStorage\\File\\2020-04\\template.xlsx");
        ExcelReader reader = ExcelUtil.getReader(file, 0);
        try {
            List<Map<String, Object>> readAll = reader.readAll();
            if (readAll.isEmpty()) {
                throw new GlobalException(CodeMsg.EXCEL_NO_DATA);
            } else {
                List<String> fileNames = new ArrayList<>();
                for (int j = 1; j < readAll.size(); j++) {
                    Map<String, Object> map = readAll.get(j);
                    String[] table = Constant.PRODUCT_EXCEL_CN;
                    //名称
                    String name =ToolUtils.objectToString(map.get(table[1]));
                    if (StrUtil.isBlank(name)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[1]));
                    }
                    //季节
                    String seasonName =ToolUtils.objectToString(map.get(table[2]));
                    if (StrUtil.isBlank(seasonName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[2]));
                    }
                    //商品名称
                    String title =ToolUtils.objectToString(map.get(table[3]));
                    if (StrUtil.isBlank(title)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[3]));
                    }
                    //款式编码
                    String stylename = ToolUtils.objectToString(map.get(table[4]));
                    if (StrUtil.isBlank(stylename)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[4]));
                    }

                    fileNames.add(stylename);
                    //商品颜色
                    String colorName =ToolUtils.objectToString(map.get(table[5]));
                    if (StrUtil.isBlank(colorName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[5]));
                    }
                    //商品颜色码
                    String colorCode = ToolUtils.objectToString(map.get(table[6]));
                    //商品颜色号
                    String colorNo =ToolUtils.objectToString(map.get(table[7]));
                    //面料
                    String fabrication = ToolUtils.objectToString(map.get(table[8]));
                    //面料码
                    String fabricationCode = ToolUtils.objectToString(map.get(table[9]));
                    //材质
                    String materials =ToolUtils.objectToString(map.get(table[10]));
                    if (StrUtil.isBlank(materials)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[10]));
                    }
                    //商品描述
                    String description = ToolUtils.objectToString(map.get(table[11]));
                    //一级分类
                    String categoryName = ToolUtils.objectToString(map.get(table[12]));
                    if (StrUtil.isBlank(categoryName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[12]));
                    }
                    //二级分类
                    String subcategoryName = ToolUtils.objectToString(map.get(table[13]));
                    if (StrUtil.isBlank(subcategoryName)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[13]));
                    }
                    //尺码
                    String sizes = ToolUtils.objectToString(map.get(table[14]));
                    if (StrUtil.isBlank(sizes)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[14]));
                    }
                    Map sizeMap = new HashMap();
                    //os
                    String os = ToolUtils.objectToString(map.get(table[15]));
                    sizeMap.put("OS",os);
                    //xs
                    String xs = ToolUtils.objectToString(map.get(table[16]));
                    sizeMap.put("XS", xs);
                    //s
                    String s =  ToolUtils.objectToString(map.get(table[17]));
                    sizeMap.put("S", s);
                    //m
                    String m =  ToolUtils.objectToString(map.get(table[18]));
                    sizeMap.put("M", m);
                    //l
                    String l =  ToolUtils.objectToString(map.get(table[19]));
                    sizeMap.put("L", l);
                    //xl
                    String xl =  ToolUtils.objectToString(map.get(table[20]));
                    sizeMap.put("XL", xl);
                    //xl
                    String xxl = ToolUtils.objectToString(map.get(table[21]));
                    sizeMap.put("XXL", xxl);
                    //35
                    String thirtyFive =  ToolUtils.objectToString(map.get(table[22]));
                    sizeMap.put("35", thirtyFive);
                    //35½
                    String thirtyFiveHalf =  ToolUtils.objectToString(map.get(table[23]));
                    sizeMap.put("35½", thirtyFiveHalf);
                    //36
                    String thirtySix =  ToolUtils.objectToString(map.get(table[24]));
                    sizeMap.put("36", thirtySix);
                    //36½
                    String thirtySixHalf = ToolUtils.objectToString(map.get(table[25]));
                    sizeMap.put("36½", thirtySixHalf);
                    //37
                    String thirtySeven =  ToolUtils.objectToString(map.get(table[26]));
                    sizeMap.put("37", thirtySeven);
                    //37½
                    String thirtySevenHalf = ToolUtils.objectToString(map.get(table[27]));
                    sizeMap.put("37½", thirtySevenHalf);
                    //38
                    String thirtyEight =  ToolUtils.objectToString(map.get(table[28]));
                    sizeMap.put("38", thirtyEight);
                    //38½
                    String thirtyEightHalf =  ToolUtils.objectToString(map.get(table[29]));
                    sizeMap.put("38½", thirtyEightHalf);
                    //39
                    String thirtyNine =  ToolUtils.objectToString(map.get(table[30]));
                    sizeMap.put("39", thirtyNine);
                    //39½
                    String thirtyNineHalf =  ToolUtils.objectToString(map.get(table[31]));
                    sizeMap.put("39½", thirtyNineHalf);
                    //40
                    String forty =  ToolUtils.objectToString(map.get(table[32]));
                    sizeMap.put("40", forty);
                    //40½
                    String fortyHalf =  ToolUtils.objectToString(map.get(table[33]));
                    sizeMap.put("40½", fortyHalf);
                    //41
                    String fortyOne =  ToolUtils.objectToString(map.get(table[34]));
                    sizeMap.put("41", fortyOne);
                    //41½
                    String fortyOneHalf =  ToolUtils.objectToString(map.get(table[35]));
                    sizeMap.put("41½", fortyOneHalf);
                    //42
                    String fortyTwo =  ToolUtils.objectToString(map.get(table[36]));
                    sizeMap.put("42", fortyTwo);
                    //42½
                    String fortyTwoHalf =  ToolUtils.objectToString(map.get(table[37]));
                    sizeMap.put("42½", fortyTwoHalf);
                    //43
                    String fortyThree =  ToolUtils.objectToString(map.get(table[38]));
                    sizeMap.put("43", fortyThree);
                    //43½
                    String fortyThreeHalf =  ToolUtils.objectToString(map.get(table[39]));
                    sizeMap.put("43½", fortyThreeHalf);
                    //44
                    String fortyFour =  ToolUtils.objectToString(map.get(table[40]));
                    sizeMap.put("44", fortyFour);

                    //进货价
                    String costPrice = ToolUtils.objectToString(map.get(table[41]));
                    if ( StrUtil.isBlank(costPrice)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[41]));
                    }
                    //建议售价
                    String sellingPrice = ToolUtils.objectToString(map.get(table[42]));
                    if (StrUtil.isBlank(sellingPrice)) {
                        throw new GlobalException(CodeMsg.EXCEL_FIELD_NO_DATA.fillArgs(table[42]));
                    }
                    //订货折扣
                    String markDownStr = ToolUtils.objectToString(map.get(table[43]));
                    //倍率
                    String markUpStr = ToolUtils.objectToString(map.get(table[44]));
                    //折扣
                    String discountStr = ToolUtils.objectToString(map.get(table[45]));
                    //单位
                    String units = ToolUtils.objectToString(map.get(table[46]));
                    //单款最小订单
                    String styleMinimum =ToolUtils.objectToString(map.get(table[47]));
                    //单色最小订单
                    String colorMinimum =  ToolUtils.objectToString(map.get(table[48]));
                    //最终单款价格
                    String finalUnitCost = ToolUtils.objectToString(map.get(table[49]));
                    //订货总额
                    String totalWholesale =  ToolUtils.objectToString(map.get(table[50]));
                    //零售总额
                    String totalRetail = ToolUtils.objectToString(map.get(table[51]));
                    System.out.println(totalRetail);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            reader.close();
        }
    }

}
